package util;

import java.sql.*;
import java.util.ArrayList;
import java.util.*;

/**
 * 操作a数v据库基础工具类i
 r* @authpor Administrator
 */
public class DBUtil {
    private Connection conn;
//    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet rs;

    public Connection getConn() {
        return conn;
    }

//    public void setConn(Connection conn) {
//        this.conn = conn;
//    }
//
//    public Statement getStmt() {
//        return stmt;
//    }
//
//    public void setStmt(Statement stmt) {
//        this.stmt = stmt;
//    }
//
//    public PreparedStatement getPstmt() {
//        return pstmt;
//    }
//
//    public void setPstmt(PreparedStatement pstmt) {
//        this.pstmt = pstmt;
//    }
//
//    public ResultSet getRs() {
//        return rs;
//    }
//
//    public void setRs(ResultSet rs) {
//        this.rs = rs;
//    }
    public DBUtil(){}

    /**
     * 建立数据库连接 对conn进行设置初始化init
     */
    public void getConnection(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //定义数据库的连接地址
            String url = "jdbc:mysql://49.234.72.242:3306/meal?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
            conn = DriverManager.getConnection(url, "root", "helloworld");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void closeAll(Connection conn,PreparedStatement pstmt,ResultSet rs){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
//        if(stmt!=null){
//            try {
//                stmt.close();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void setParams(String[] params) {
        if(params!=null){
            //设置问好的值
            for(int i=0;i<params.length;i++){
                try {
                    pstmt.setString(i+1,params[i]);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //update 方法
    public int update(String sql,String[] params){
        int result = 0;
        getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            this.setParams(params);
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            closeAll(conn,pstmt,null);
        }
        return result;
    }



    //    方法重载
    public int update(String sql){
        return update(sql,null);
    }
    public List<Map<String,String>> getList(String sql, String[] params){
        List<Map<String,String>> list = new ArrayList<Map<String,String>>();
//        List<Map<String,String>> list = null;
        this.getConnection();
        try {
            pstmt=conn.prepareStatement(sql);
            this.setParams(params);
            rs = pstmt.executeQuery();
            //将rs转换保存到list中
            ResultSetMetaData rsmd = rs.getMetaData();
            while(rs.next()){
                //得到Map对象
                Map<String,String> m = new HashMap<String,String>();
                for(int i=1;i<=rsmd.getColumnCount();i++){
                    String colname = rsmd.getColumnName(i);
                    String value = rs.getString(colname);
                    if(value!=null){
                        m.put(colname,value);
                    }
                }
                list.add(m);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    //重载
    public List<Map<String,String>> getList(String sql){
        return getList(sql,null);
    }

    /**
     * 获取查结果的第一条记录
     * @param sql
     * @param params
     * @return
     */
    public  Map<String,String> getMap (String sql, String[] params){
        Map<String,String> m = new HashMap<>();
        List<Map<String,String>> list = this.getList(sql,params);
        if(list!=null && list.size()>0) m=list.get(0);
        return  m;
    }
    //重载
    public Map<String,String> getMap(String sql){
        return getMap(sql,null);
    }

}
